package com.jiuxian.scheduling.dao;

import com.jiuxian.scheduling.util.DataSourceUtil;
import com.jiuxian.scheduling.util.SiteUtil;
import com.jiuxian.scheduling.vo.LogInfoVO;
import org.apache.commons.lang3.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by wangjingqing on 2017/12/25.
 */
public class LogDao {
    Logger logger = LoggerFactory.getLogger(LogDao.class);

    /**
     * 判断表是否存在<br>
     * @param tableName 表名
     * @return boolean
     */
    public boolean isExistTable(String tableName){
        try {
            ResultSet rs = DataSourceUtil.getConnection().getMetaData().getTables(null, null, tableName, null);
            if(rs.next()){
                return true;
            }
        } catch (SQLException e) {
            logger.error("判断表是否存在异常："+tableName,e);
        }
        return false;
    }

    /**
     * 创建表<br>
     * @return boolean
     */
    public boolean createLogTable(){
        String sql = "CREATE TABLE task_log(\n" +
                "id INTEGER  GENERATED BY DEFAULT AS IDENTITY(START WITH 1) NOT NULL PRIMARY KEY,\n" +
                "name VARCHAR(150),\n" +
                "taskTime INTEGER,\n" +
                "state BIT(1),\n" +
                "detais LONGVARCHAR,\n" +
                "creatTime VARCHAR(25)\n" +
                ")";
        try {
            Statement statement = DataSourceUtil.getConnection().createStatement();
            statement.execute(sql);
            DataSourceUtil.getConnection().commit();
        } catch (SQLException e) {
            logger.error("创建表失败：",e);
        }
        return false;
    }

    
    public int insertLogInfo(LogInfoVO logInfoVO){
        try {
            String sql = "INSERT INTO TASK_LOG (name,taskTime,state,detais,creatTime) VALUES(?,?,?,?,?)";
            PreparedStatement statement = DataSourceUtil.getConnection().prepareStatement(sql);
            statement.setString(1,logInfoVO.getName());
            statement.setInt(2,logInfoVO.getTaskTime());
            statement.setBoolean(3,logInfoVO.getState());
            statement.setString(4,logInfoVO.getDetails());
            statement.setString(5,logInfoVO.getCreatTime());
            int state = statement.executeUpdate();
            DataSourceUtil.getConnection().commit();
            return state;
        } catch (SQLException e) {
            logger.error("插入LOG信息失败：",e);
        }
        return 0;
    }

    public List<LogInfoVO> selectLogInfos(String name,Date startTime,Date endTime){
        List<LogInfoVO> lists = new ArrayList<>();
        try {
            StringBuffer sql = new StringBuffer("SELECT top 150 * FROM TASK_LOG");
            boolean isAddAnd = false;
            if(StringUtils.isNotBlank(name)){
                sql.append(" WHERE NAME =").append(name);
                isAddAnd = true;
            }
            if(startTime != null){
                if(isAddAnd){
                    sql.append(" and ");
                }else{
                    sql.append(" WHERE ");
                    isAddAnd = true;
                }
                sql.append(" creatTime>").append(SiteUtil.convertDate(startTime,SiteUtil.FORMAT_SECOND));
            }
            if(endTime != null){
                if(isAddAnd){
                    sql.append(" and ");
                }else{
                    sql.append(" WHERE ");
                }
                sql.append(" creatTime <").append(SiteUtil.convertDate(endTime,SiteUtil.FORMAT_SECOND));
            }

            sql.append(" order by id desc");
            Statement statement = DataSourceUtil.getConnection().createStatement();
            ResultSet rs = statement.executeQuery(sql.toString());
            while(rs.next()){
                LogInfoVO info = new LogInfoVO();
                info.setName(rs.getString("name"));
                info.setTaskTime(rs.getInt("taskTime"));
                info.setState(rs.getBoolean("state"));
                info.setDetails(rs.getString("detais"));
                info.setCreatTime(rs.getString("creatTime"));
                lists.add(info);
            }
        } catch (SQLException e) {
            logger.error("查询异常：",e);
        }
        return lists;
    }
}
